package com.tgb.lk.table;

import java.sql.*;
import javax.sql.rowset.CachedRowSet;

public class SQLTableManager extends TableManager {

	private SQLTableManager() {
	}

	public static TableManager getInstance() {
		return _instance;
	}

	@Override
	public Table getTableInfo(String name) {
		Table table = new Table();
		table.setName(StringUtil.ruleJavaName(name));
		TableConfig config = TableConfig.getInstance();
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			conn = DBManager.getInstance().getConnection(
					config.getDbConnectString(), config.getDbUsername(),
					config.getDbPasswd());
			st = conn.createStatement(1005, 1007);
			String sql = "SELECT col.name,typ.name as data_type,col.max_length length,col.is_nullable ,ep.value memo "
					+ " FROM sys.columns col left join sys.types typ on (col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id) ";
			sql = sql
					+ " left join sys.extended_properties ep on (col.object_id = ep.major_id and col.column_id=ep.minor_id ) "
					+ " WHERE col.object_id =(SELECT object_id FROM sys.tables WHERE name = '"
					+ name.toUpperCase() + "')";
			rs = st.executeQuery(sql);
			int num;
			for (num = 0; rs.next(); num++) {
				;
			}
			rs.beforeFirst();
			table.setColumnNum(num);
			TableColumn[] columns = new TableColumn[num];
			String tmp = "";
			String c[] = new String[num];
			int t[] = new int[num];
			boolean[] nullable = new boolean[num];
			int i = 0;
			while (rs.next()) {
				c[i] = rs.getString("name").toLowerCase().trim();
				tmp = rs.getString("data_type").toLowerCase().trim();
				nullable[i] = ("0".equals(rs.getString("is_nullable"))) ? false
						: true;

				TableColumn column = new TableColumn();
				column.setId(i + 1);
				column.setColumnName(c[i]);
				column.setColumnType(tmp);
				column.setNullable(nullable[i]);
				column.setKey(false);
				column.setLength(rs.getLong("length"));
				column.setMemo(rs.getString("memo"));
				columns[i] = column;

				t[i++] = DataType.getInstance().getTypeMap(tmp);
			}
			table.setColumnName(c);
			table.setColumnType(t);
			table.setNullable(nullable);
			table.setColumns(columns);
			DBManager.getInstance().cleanup(null, null, rs);
			rs = st
					.executeQuery("select c.name column_name from dbo.sysindexkeys k,dbo.syscolumns c,dbo.sysobjects t  where c.id = t.id and c.colid=k.colid and c.id=k.id and t.name='"
							+ name.toUpperCase() + "'");
			int[] keyPos = getKeyPos(c, getKey(rs));
			table.setKeyPos(keyPos);
			for (int j : keyPos) {
				columns[j].setKey(true);
			}

			// 获得主键名称
			// ResultSet rs2 = null;
			DBManager.getInstance().cleanup(null, null, rs);
			rs = st
					.executeQuery("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='"
							+ name.toUpperCase() + "'");
			while (rs.next()) {
				table.setPkName(rs.getString("COLUMN_NAME").toLowerCase()
						.trim());
			}

		} catch (Exception e) {
			Log.log(e);
		} finally {
			DBManager.getInstance().cleanup(conn, st, rs);
		}
		return table;
	}

	@Override
	public String[] getAllTableName() {
		String tables[] = null;
		String sql = "select name table_name from dbo.sysobjects where xtype='u' order by name ";
		CachedRowSet crs = DBManager.getInstance().executeQuery(sql, null);
		try {
			tables = new String[crs.size()];
			int i = 0;
			while (crs.next()) {
				tables[i++] = crs.getString("table_name").toLowerCase();
			}
		} catch (Exception e) {
			Log.log(e);
		}
		return tables;
	}

	private static SQLTableManager _instance = new SQLTableManager();

	// private String getPKName(String tableName)
	// {
	// Config config = Config.getInstance();
	// String
	// sql="SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='"+
	// tableName +"'";
	// Connection conn = null;
	// Statement st = null;
	// ResultSet rs = null;
	// try {
	// conn = DBManager.getInstance().getConnection(config.getDbConnectString(),
	// config.getDbUsername(), config.getDbPasswd());
	// st = conn.createStatement(1005, 1007);
	// rs = st.executeQuery(sql);
	// return rs.getString("COLUMN_NAME");
	//            
	// } catch (Exception e) {
	// e.printStackTrace();
	// }
	//    	
	// return null;
	// }

}